package cn.store.dao.daoImp;

import cn.store.dao.UserDao;
import cn.store.domain.User;
import cn.store.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImp implements UserDao {

	@Override
	public void userRegist(User user) throws SQLException {
		String sql="INSERT INTO user VALUES(?,?,?,?,?,?,?,?,?,?)";

		System.out.println("-----dao"+user);
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={user.getUid(),user.getUsername(),user.getPassword(),user.getName(),user.getEmail(),user.getTelephone(),user.getBirthday(),user.getSex(),user.getState(),user.getCode()};
		qr.update(sql, params);
		
	}

	@Override
	public void updateUser(User user) throws SQLException{
		String sql="update user set username=? , password=? ,name =? ,email=?, telephone =? ,birthday =? ,sex=? ,state=? ,code= ? where uid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={user.getUsername(),user.getPassword(),user.getName(),user.getEmail(),user.getTelephone(),user.getBirthday(),user.getSex(),user.getState(),user.getCode(),user.getUid()};
		qr.update(sql,params);
		System.out.println("参数"+params);
	}

	//根据密码账户查询用户是否存在
	@Override
	public User userLogin(User user) throws SQLException {
		String sql="select * from user where username=?  and password= ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<User>(User.class),user.getUsername(),user.getPassword());
	}

	@Override
	public User userRegistcheck(String username) throws SQLException {
		String sql="select * from user where username=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<User>(User.class),username);
	}

	@Override
	public List<User> findUsersWithPage(Map<String, Object> searchWords, int startIndex, int pageSize) throws SQLException {
		StringBuffer sql = new StringBuffer("select * from user");

		boolean flag = false;
		for (String key:searchWords.keySet()) {
			if(searchWords.get(key) != null){
				if (flag == false){
					sql.append(" where ");
					flag = true;
				}else {
					sql.append(" and ");
				}

				if("state".equals(key)){
					sql.append(key);
					sql.append("=");
					sql.append(searchWords.get(key));
				} else{
					sql.append(key);
					sql.append(" like '%");
					sql.append(searchWords.get(key));
					sql.append("%' ");
				}
			}

		}
		sql.append(" limit ?,?");
		System.out.println(sql.toString());
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		List<User> list = qr.query(sql.toString(), new BeanListHandler<User>(User.class), startIndex,pageSize);
		return list;
	}

	@Override
	public int findTotalRecords(Map<String, Object> searchWords) throws SQLException {
		StringBuffer sql = new StringBuffer("select count(*) from user");
		boolean flag = false;
		for (String key:searchWords.keySet()) {
			if(searchWords.get(key) != null){
				if (flag == false){
					sql.append(" where ");
					flag = true;
				}else {
					sql.append(" and ");
				}

				if("state".equals(key)){
					sql.append(key);
					sql.append("=");
					sql.append(searchWords.get(key));
				} else{
					sql.append(key);
					sql.append(" like '%");
					sql.append(searchWords.get(key));
					sql.append("%' ");
				}
			}

		}
		System.out.println(sql.toString());
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		Long num = (Long) qr.query(sql.toString(),new ScalarHandler());
		return num.intValue();
	}

	@Override
	public User findUserById(String id) throws SQLException {
		String sql = "select * from user where uid=?";
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		User user = qr.query(sql, new BeanHandler<User>(User.class),id);
		return user;
	}

	@Override
	public int deleteUser(String uid) throws SQLException {
		String sql="delete from user where uid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		int i = qr.update(sql,uid);
		System.out.println("records:"+i);
		return i;
	}

}
